package com.example.demo;

import java.sql.*;

/**
 * JDBC基础演示类
 * 展示JDBC的五步查询流程和防SQL注入的最佳实践
 */
public class JdbcDemo {

    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://localhost:3306/userdb?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "Craftsman@2025";

    public static void main(String[] args) {
        JdbcDemo demo = new JdbcDemo();

        System.out.println("=== JDBC 五步查询流程演示 ===");
        demo.demonstrateJdbcFiveSteps();

        System.out.println("\n=== SQL注入防护演示 ===");
        demo.demonstrateSqlInjectionProtection();

        System.out.println("\n=== 批量查询演示 ===");
        demo.demonstrateBatchQuery();
    }

    /**
     * 演示JDBC五步查询流程
     */
    public void demonstrateJdbcFiveSteps() {
        System.out.println("演示查询用户 'alice' 的信息：");

        // 第1步：获取连接（Connection）
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            System.out.println("✓ 第1步：成功获取数据库连接");

            // 第2步：创建预编译语句（PreparedStatement）
            String sql = "SELECT * FROM users WHERE username = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            System.out.println("✓ 第2步：创建预编译SQL语句");

            // 第3步：设置参数
            ps.setString(1, "alice");
            System.out.println("✓ 第3步：设置查询参数 username = 'alice'");

            // 第4步：执行查询
            ResultSet rs = ps.executeQuery();
            System.out.println("✓ 第4步：执行查询");

            // 第5步：遍历结果集
            System.out.println("✓ 第5步：处理查询结果");
            if (rs.next()) {
                String name = rs.getString("username");
                String email = rs.getString("email");
                System.out.println("  找到用户：" + name + " : " + email);
            } else {
                System.out.println("  未找到用户 'alice'");
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败：" + e.getMessage());
        }
    }

    /**
     * 演示SQL注入防护
     */
    public void demonstrateSqlInjectionProtection() {
        System.out.println("演示SQL注入攻击和防护：");

        // 恶意输入
        String maliciousInput = "' OR '1'='1";

        // 危险示例：直接拼接SQL（不要在实际项目中使用）
        System.out.println("❌ 危险示例：直接拼接SQL");
        demonstrateUnsafeQuery(maliciousInput);

        // 安全示例：使用PreparedStatement
        System.out.println("\n✅ 安全示例：使用PreparedStatement");
        demonstrateSafeQuery(maliciousInput);
    }

    /**
     * 演示不安全的查询（仅用于教学）
     */
    private void demonstrateUnsafeQuery(String username) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            // ⚠️ 危险：直接拼接SQL字符串
            String sql = "SELECT * FROM users WHERE username = '" + username + "'";
            System.out.println("  执行的SQL：" + sql);

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            int count = 0;
            while (rs.next()) {
                count++;
                System.out.println("  查询到用户：" + rs.getString("username"));
            }
            System.out.println("  总共查询到 " + count + " 个用户（可能泄露所有用户信息！）");

        } catch (SQLException e) {
            System.err.println("  查询失败：" + e.getMessage());
        }
    }

    /**
     * 演示安全的查询
     */
    private void demonstrateSafeQuery(String username) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            // ✅ 安全：使用PreparedStatement
            String sql = "SELECT * FROM users WHERE username = ?";
            System.out.println("  执行的SQL：" + sql);
            System.out.println("  参数值：" + username);

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();

            int count = 0;
            while (rs.next()) {
                count++;
                System.out.println("  查询到用户：" + rs.getString("username"));
            }
            System.out.println("  总共查询到 " + count + " 个用户（安全！）");

        } catch (SQLException e) {
            System.err.println("  查询失败：" + e.getMessage());
        }
    }

    /**
     * 演示批量查询
     */
    public void demonstrateBatchQuery() {
        System.out.println("演示批量查询所有用户：");

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            String sql = "SELECT id, username, email FROM users ORDER BY id";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            System.out.println("用户列表：");
            System.out.println("ID\t用户名\t\t邮箱");
            System.out.println("--\t----\t\t----");

            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String email = rs.getString("email");
                System.out.printf("%d\t%s\t\t%s%n", id, username, email);
            }

        } catch (SQLException e) {
            System.err.println("批量查询失败：" + e.getMessage());
        }
    }

    /**
     * 演示常用setXXX方法
     */
    public void demonstrateSetMethods() {
        System.out.println("演示PreparedStatement的常用setXXX方法：");

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            String sql = "INSERT INTO users (username, password, email, age, created_date) VALUES (?, ?, ?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);

            // 演示不同的setXXX方法
            ps.setString(1, "newuser"); // setString - 字符串
            ps.setString(2, "password123"); // setString - 字符串
            ps.setString(3, "user@example.com"); // setString - 字符串
            ps.setInt(4, 25); // setInt - 整型
            ps.setDate(5, new java.sql.Date(System.currentTimeMillis())); // setDate - 日期

            System.out.println("✓ 演示了setString、setInt、setDate等方法");

        } catch (SQLException e) {
            System.err.println("演示失败：" + e.getMessage());
        }
    }
}